/*
 * TermTable.java - SQL operations with the table 'term'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;

import dbindex.Language;
import wikipedia.util.StringUtil;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;


/** Routines to work with the table 'class' in the database 'context'.
 */
public class TermTable {
   
    /** term's id in ontology */
    public int      term_id;
    
    /** lang id of term */
    public Language lang;
    
    /** title of an ontology element */
    public String   text;
    
    /** description of an ontology element */
    public String   desc;
    
    /** Creates a new instance of TermTable */
    public TermTable() {
    }
    
    /** Inserts element to the 'term' table.
     * 
     * SQL example:
     * INSERT INTO term (lang_id, text, description) VALUES (0, "title12", "desc12");
     * UPDATE term SET lang_id=0, text='title12_hello', description='desc12_hello' WHERE id=1;
     *
     *  @param lang   language of term
     */
    public static void insert (java.sql.Connection conn,Language lang,String text_source,String desc_source) {
          
        Statement   s = null;
        ResultSet   rs= null;
        int         term_id=0;
        String      text, desc, str_sql = null;
        TermTable   term_temp = null;
        
        text = StringUtil.escapeChars(text_source);
        desc = StringUtil.escapeChars(desc_source);
        
        try 
        {
            s = conn.createStatement ();
            term_id = getID(conn, lang, text_source);
            if (0 == term_id)
            {
                str_sql = "INSERT INTO term (lang_id, text, description) "+ 
                            "VALUES (" + lang.toInt() + 
                            ",'"+ text + "','"+ desc + "')";
                s.executeUpdate (str_sql);
            } /*else
            {
	            str_sql = "UPDATE term SET " + 
	            				"lang_id="+ lang.toInt() + 
                                ",text ='"+ text + 
	            				"',description='" + desc +
	            				" WHERE id = "+ term_temp.term_id;
            }*/
            
        }catch(SQLException ex) {
            System.err.println("SQLException (context.TermTable.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    /** Gets ID of the term (by language, title and description of the term), 
     * if it is absent then creates it and returns ID. 
     */
    public static int insertAndGetID (java.sql.Connection conn,Language lang,String text,String desc) 
    {
        int term_id = getID(conn, lang, text);
        if(0 == term_id) {
            insert(conn, lang, text, desc);
            term_id = getID(conn, lang, text);
        }
        return term_id;
    }
    
    /** Selects term's id from the table 'term' by the (lang, text, text).
     *          
     *  SQL:
     *  SELECT id FROM term WHERE lang_id=0 AND text='title12' AND description="desc12"
     *  
     *  @param lang         languge of the term
     *  @param text         title of the term
     *  @return term's ID, if it is absent then return 0
     */
    public static int getID (java.sql.Connection conn,Language lang,String text) {
        
        Statement   s = null;
        ResultSet   rs= null;
        int         id = 0;
        String      str_sql = null;
        
        text = StringUtil.escapeChars(text);
        //desc = StringUtil.escapeChars(desc);
        
        try {
            s = conn.createStatement ();
            //str_sql = "SELECT id FROM term WHERE lang_id="+lang.toInt()+" AND text='"+text+"' AND description='"+desc+"'";
            str_sql = "SELECT id FROM term WHERE lang_id="+lang.toInt()+" AND text='"+text+"'";
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                id = rs.getInt("id");
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassTable.java getID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return id;
    }
    
    /** Selects term's (lang, text, description) from the table 'term' by term's ID.
     *          
     *  SQL:
     *  SELECT lang_id, text, description FROM term WHERE id=2
     */
    public static TermTable getByID (java.sql.Connection conn,int id) {
        
        TermTable   term = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            // SELECT lang_id, text, description FROM term WHERE id=2
            str_sql = "SELECT lang_id, text, description FROM term WHERE id="+id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                term = new TermTable();
                term.term_id = id;
                term.lang = Language.get(rs.getInt("lang_id"));
                term.text = rs.getString("text");
                term.desc = rs.getString("description");
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassTable.java getByID()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return term;
    }
    
    /** Deletes term's rows by (lang, text) from the table 'term'.
     *  SQL:        
     *  DELETE FROM term WHERE id=2;
     */
    public static void delete (java.sql.Connection conn,Language lang,String text) {
        
        TermTable   term = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        text = StringUtil.escapeChars(text);
        //desc = StringUtil.escapeChars(desc);
        
        try {
            s = conn.createStatement ();
            //str_sql = "DELETE FROM term WHERE lang_id="+lang.toInt()+" AND text='"+text+"' AND description='"+desc+"'";
            str_sql = "DELETE FROM term WHERE lang_id="+lang.toInt()+" AND text='"+text+"'";
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.ClassTable.java delete()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
}
